/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.absisgroup.nt;

import com.absisgroup.nsc.utilities.GenericServlet;
import com.absisgroup.nsc.utilities.Util;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.json.JSONArray;
import org.json.JSONObject;

/**
 *
 * @author Hue
 */
public class DanhSachPhieuBanHang extends GenericServlet {

    @Override
    public int process(final Connection con, final HttpServletRequest request, final HttpServletResponse response) throws Exception {
        int start = Util.toInt(request.getParameter("start"));
        int limit = Util.toInt(request.getParameter("limit"));
        writeToResponse(response, toJSONObject(con, start, limit));
        
        return DONT_RUN_JSP;
    }
    
    private String toJSONObject(final Connection con, int start, int limit) throws Exception {
        final JSONArray root = new JSONArray();
        String strQuery = "select ORDER_PK, ORDER_DATE, ORDER_CUSTOMER, ORDER_ADDRESS, ORDER_PHONE, ORDER_SELLER, ORDER_NO " + 
                          "from ORDERS order by ORDER_DATE desc, ORDER_NUMBER desc";
        PreparedStatement pStmt = con.prepareStatement("select * from (" + strQuery + ") tmp limit ?, ?");
        pStmt.setInt(1, start);
        pStmt.setInt(2, limit);
        ResultSet rs = pStmt.executeQuery();
        while(rs.next()) {
            JSONObject jsEntity = new JSONObject();
            jsEntity.put("orderPK", rs.getString("ORDER_PK"));
            jsEntity.put("date", Util.toString(rs.getDate("ORDER_DATE"), "dd/MM/yyyy"));
            jsEntity.put("customer", rs.getString("ORDER_CUSTOMER"));
            jsEntity.put("address", rs.getString("ORDER_ADDRESS"));
            jsEntity.put("phone", rs.getString("ORDER_PHONE"));
            jsEntity.put("seller", rs.getString("ORDER_SELLER"));
            jsEntity.put("orderNo", rs.getString("ORDER_NO"));
            root.put(jsEntity);
        }
        pStmt.close();

        final JSONObject jsObj = new JSONObject();
        jsObj.put("totalCount", getTotalCount(con));
        jsObj.put("root", root);
        
        return jsObj.toString();
    }
    
    private long getTotalCount(Connection con) throws SQLException {
        PreparedStatement pStmt = con.prepareStatement("select count(*) from ORDERS");
        ResultSet rs = pStmt.executeQuery();
        long count = 0;
        if(rs.next()) {
            count = rs.getLong(1);
        }
        pStmt.close();

        return count;
    }
    
    @Override
    protected boolean useConnection() {
        return true;
    }
}
